/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package baiwa.rta.dao.Impl;

import static java.text.MessageFormat.format;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import baiwa.rta.dao.ConciergeDepositDao;
import baiwa.rta.dao.mapping.ConciergeDepositDetailMapping;
import baiwa.rta.dao.mapping.ConciergeDepositHeaderMapping;
import baiwa.rta.model.DataTableModel;
import baiwa.rta.model.RMcddtlModel;
import baiwa.rta.model.RMcdhdrModel;
import baiwa.rta.model.TowerModel;
import baiwa.rta.utils.CommonUtils;

import java.text.MessageFormat;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;

/**
 *
 * @author jirapatjantsakool
 */
@Repository
public class ConciergeDepositDaoImpl implements ConciergeDepositDao {
    
    private static final Logger logger = Logger.getLogger(ConciergeDepositDaoImpl.class);

    @Autowired
    JdbcTemplate jdbcTemplate;

    private static final String SQL_QUERY_RMCDHDR_ALL = "SELECT * FROM rmcdhdr ";
    private static final String SQL_QUERY_RMCDHDR_BY_ID = "SELECT cd.*, rm.* FROM rmcdhdr cd INNER JOIN rmrmlst rm ON cd.rlrmid = rm.rlrmid WHERE  cd.cdhid = ? ";

    @Override
    public List<RMcdhdrModel> findAll() {

        List<RMcdhdrModel> list = jdbcTemplate.query(SQL_QUERY_RMCDHDR_ALL, new Object[]{}, new ConciergeDepositHeaderMapping());

        return list == null ? new ArrayList<RMcdhdrModel>() : list;
    }

    @Override
    public RMcdhdrModel findById(Integer id) {
        List<RMcdhdrModel> model = jdbcTemplate.query(SQL_QUERY_RMCDHDR_BY_ID, new Object[]{id}, new ConciergeDepositHeaderMapping());

        return CommonUtils.isNullOrEmtry(model)|| model.size() <= 0 ? new RMcdhdrModel() : model.get(0);
    }

    private static final String SQL_INSERT_RMCDHDR = "INSERT INTO rmcdhdr(cdhdocno,rlrmid,cdhdocstatus,cdhrcvdat,cdhreqto,cdhreqby,cdhreqtel,cdhreqemail,cdhdephoby,cdhdephodat,cdhdeprcvby,cdhdeprcvdat,cdhrcvrcvby,cdhrcvrcvdat,cdhrcvhoby,cdhrcvhodat,cdhcreby,cdhcredat,cdhupdby,cdhupddat) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";

    @Override
    public void create(final RMcdhdrModel model) throws Exception {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(SQL_INSERT_RMCDHDR), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setString(++i, model.getCdhdocno() == null ? null : model.getCdhdocno());
                ps.setInt(++i, model.getRlrmid());
                ps.setString(++i, model.getCdhdocstatus() == null ? null : model.getCdhdocstatus());
                ps.setDate(++i, model.getCdhrcvdat() == null ? null : new java.sql.Date(model.getCdhrcvdat().getTime()));
                ps.setString(++i, model.getCdhreqto() == null ? null : model.getCdhreqto());
                ps.setString(++i, model.getCdhreqby() == null ? null : model.getCdhreqby());
                ps.setString(++i, model.getCdhreqtel() == null ? null : model.getCdhreqtel());
                ps.setString(++i, model.getCdhreqemail() == null ? null : model.getCdhreqemail());
                ps.setString(++i, model.getCdhdephoby() == null ? null : model.getCdhdephoby());
                ps.setDate(++i, model.getCdhdephodat() == null ? null : new java.sql.Date(model.getCdhdephodat().getTime()));
                ps.setString(++i, model.getCdhdeprcvby() == null ? null : model.getCdhdeprcvby());
                ps.setDate(++i, model.getCdhdeprcvdat() == null ? null : new java.sql.Date(model.getCdhdeprcvdat().getTime()));
                ps.setString(++i, model.getCdhrcvrcvby() == null ? null : model.getCdhrcvrcvby());
                ps.setDate(++i, model.getCdhrcvrcvdat() == null ? null : new java.sql.Date(model.getCdhrcvrcvdat().getTime()));
                ps.setString(++i, model.getCdhrcvhoby() == null ? null : model.getCdhrcvhoby());
                ps.setDate(++i, model.getCdhrcvhodat() == null ? null : new java.sql.Date(model.getCdhrcvhodat().getTime()));
                ps.setString(++i, model.getCdhcreby() == null ? null : model.getCdhcreby());
                ps.setDate(++i, model.getCdhcredat() == null ? null : new java.sql.Date(model.getCdhcredat().getTime()));
                ps.setString(++i, model.getCdhupdby() == null ? null : model.getCdhupdby());
                ps.setDate(++i, model.getCdhupddat() == null ? null : new java.sql.Date(model.getCdhupddat().getTime()));
                return ps;
            }

        }, keyHolder);

    }

    private static final String SQL_UPDATE_RMCDHDR = "UPDATE rmcdhdr SET cdhdocno=?,rlrmid=?,cdhdocstatus=?,cdhrcvdat=?,cdhreqto=?,cdhreqby=?,cdhreqtel=?,cdhreqemail=?,cdhdephoby=?,cdhdephodat=?,cdhdeprcvby=?,cdhdeprcvdat=?,cdhrcvrcvby=?,cdhrcvrcvdat=?,cdhrcvhoby=?,cdhrcvhodat=?,cdhcreby=?,cdhcredat=?,cdhupdby=?,cdhupddat=? WHERE cdhid=?";

    @Override
    public boolean update(RMcdhdrModel model) {
        int row = jdbcTemplate.update(format(SQL_UPDATE_RMCDHDR),
                new Object[]{
                    model.getCdhdocno(),
                    model.getRlrmid(),
                    model.getCdhdocstatus(),
                    model.getCdhrcvdat(),
                    model.getCdhreqto(),
                    model.getCdhreqby(),
                    model.getCdhreqtel(),
                    model.getCdhreqemail(),
                    model.getCdhdephoby(),
                    model.getCdhdephodat(),
                    model.getCdhdeprcvby(),
                    model.getCdhdeprcvdat(),
                    model.getCdhrcvrcvby(),
                    model.getCdhrcvrcvdat(),
                    model.getCdhrcvhoby(),
                    model.getCdhrcvhodat(),
                    model.getCdhcreby(),
                    model.getCdhcredat(),
                    model.getCdhupdby(),
                    model.getCdhupddat(),
                    model.getCdhid()
                });
        return row > 0;
    }

    private static final String SQL_DELETE_RMCDHDR = "DELETE FROM rmcdhdr WHERE cdhid=?";

    @Override
    public boolean delete(RMcdhdrModel model) {
        int row = jdbcTemplate.update(format(SQL_DELETE_RMCDHDR), new Object[]{model.getCdhid()});
        return row > 0;
    }

    private static final String SQL_QUERY_RMCDDTL_BY_ID = "SELECT * FROM rmcddtl cd WHERE cdhid = ? ";

    @Override
    public RMcddtlModel findDetailById(Integer id) {
        List<RMcddtlModel> model = jdbcTemplate.query(SQL_QUERY_RMCDDTL_BY_ID, new Object[]{id}, new ConciergeDepositDetailMapping());

        return model == null || model.size() <= 0 ? new RMcddtlModel() : model.get(0);
    }

    private static final String SQL_INSERT_RMCDDTL = "INSERT INTO rmcddtl(cdhid,cdhdocno,cdddesc,cddqty,cddremark,cddcreby,cddcredat,cddupdby,cddupddat) VALUES(?,?,?,?,?,?,?,?,?)";

    @Override
    public void create(final RMcddtlModel model) throws Exception {

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(new PreparedStatementCreator() {

            @Override
            public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                PreparedStatement ps = connection.prepareStatement(format(SQL_INSERT_RMCDDTL), Statement.RETURN_GENERATED_KEYS);
                int i = 0;
                ps.setInt(++i, model.getCdhid() == null ? null : model.getCdhid());
                ps.setString(++i, model.getCdhdocno() == null ? null : model.getCdhdocno());
                ps.setString(++i, model.getCdddesc() == null ? null : model.getCdddesc());
                ps.setString(++i, model.getCddqty() == null ? null : model.getCddqty());
                ps.setString(++i, model.getCddremark() == null ? null : model.getCddremark());
                ps.setString(++i, model.getCddcreby() == null ? null : model.getCddcreby());
                ps.setDate(++i, model.getCddcredat() == null ? null : new java.sql.Date(model.getCddcredat().getTime()));
                ps.setString(++i, model.getCddupdby() == null ? null : model.getCddupdby());
                ps.setDate(++i, model.getCddupddat() == null ? null : new java.sql.Date(model.getCddupddat().getTime()));

                return ps;
            }

        }, keyHolder);
    }

    private static final String SQL_UPDATE_RMCSDTL = "UPDATE rmcsdtl SET cdhid=?,cdhdocno=?,cddesc=?,cddqty=?,cddremark=?,cddcreby=?,cddcredat=?,cddupdby=?,cddupddat=? WHERE cddid=?";

    @Override
    public boolean update(RMcddtlModel model) {
        int row = jdbcTemplate.update(format(SQL_UPDATE_RMCSDTL),
                new Object[]{
                    model.getCdhid(),
                    model.getCdhdocno(),
                    model.getCdddesc(),
                    model.getCddqty(),
                    model.getCddremark(),
                    model.getCddcreby(),
                    model.getCddcredat(),
                    model.getCddupdby(),
                    model.getCddupddat(),
                    model.getCddid()
                });
        return row > 0;
    }

    private static final String SQL_DELETE_RMCSDTL = "DELETE FROM rmcsdtl WHERE csdtlid=?";

    @Override
    public boolean delete(RMcddtlModel model) {
        int row = jdbcTemplate.update(format(SQL_DELETE_RMCSDTL), new Object[]{model.getCddid()});
        return row > 0;
    }

    private static final String SQL_SELECT_RMCSDTL_BY_CDHDOCNO = "SELECT * FROM rmcddtl WHERE cdhdocno = ? ";

    @Override
    public List<RMcddtlModel> findDetailByCdhdocNo(String cdhdocno) {
        List<RMcddtlModel> model = jdbcTemplate.query(SQL_SELECT_RMCSDTL_BY_CDHDOCNO, new Object[]{cdhdocno}, new ConciergeDepositDetailMapping());

        return model == null || model.size() <= 0 ? new ArrayList<RMcddtlModel>() : model;
    }

    @Override
    public DataTableModel<RMcdhdrModel> findDataTable(RMcdhdrModel model, int iDisplayStart, int iDisplayLength) {
        
    	DataTableModel<RMcdhdrModel> dataTableModel = new DataTableModel<RMcdhdrModel>();
        String sqlLimit = MessageFormat.format(" limit {0},{1} ", new Object[]{iDisplayStart, iDisplayLength});
        
        TowerModel tower = model.getTower();
        
        /* --- Where Condition  ---*/
        String sql_where = ""; 
        String sql_join = " LEFT JOIN RMRMLST TW ON TW.RLRMID = ";
        /* --- Room Condition  ----*/
        if (StringUtils.isNotBlank(tower.getTower())) {
            sql_where = sql_where.concat(" AND TW.RLSTTOWER = '" + tower.getTower() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getFloor())) {
        	sql_where = sql_where.concat(" AND TW.RLSTFLOOR = '" + tower.getFloor() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getUnitNo())) {
        	sql_where = sql_where.concat(" AND TW.RLSTUNITNO = '" + tower.getUnitNo() + "' ");
        }
        if (StringUtils.isNotBlank(tower.getAddress())) {
        	sql_where = sql_where.concat(" AND TW.RLSTADDR = '" + tower.getAddress() + "' ");
        }
        
        /*--- Other Condition ----*/
        if (StringUtils.isNotBlank(model.getCdhdocno())) {
        	sql_where = sql_where.concat(" AND A.CDHDOCNO LIKE '%" + model.getCdhdocno() + "%' ");
        }
        if (StringUtils.isNotBlank(model.getCdhdocstatus())) {
        	sql_where = sql_where.concat(" AND A.CDHDOCSTATUS LIKE '%" + model.getCdhdocstatus() + "%' ");
        }
        if (StringUtils.isNotBlank(model.getCdhreqto())) {
            sql_where = sql_where.concat(" AND A.CDHREQTO = '" + model.getCdhreqto() + "' ");
        }
        
          /*--- Initial Query ---*/
		String sqlQuery = " SELECT A.* , TW.* FROM rmcdhdr A " + sql_join +" A.RLRMID " +
				" WHERE 1=1 " + sql_where + sqlLimit;
		String sqlCount = "SELECT COUNT(1) FROM rmcdhdr A " + sql_join +" A.RLRMID " +
				" WHERE 1=1"  + sql_where;
		
		// --- #1 Get data for datatable by limit row
		List<RMcdhdrModel> list = jdbcTemplate.query(sqlQuery, new ConciergeDepositHeaderMapping());
		
		// --- #2 Get row number
		int count = jdbcTemplate.queryForInt(sqlCount);
		
        dataTableModel.setItems(list);
        dataTableModel.setTotal(count);
        dataTableModel.setIndex(iDisplayStart);
        dataTableModel.setLength(iDisplayLength);

        logger.debug("SQL : " + sqlQuery);
        return dataTableModel;
    }

}
